using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace StudentManagementApplication.DAO
{
    class SubjectDetailDAO : BaseDAO
    {
        public Entities.SubjectDetail CreateSubjectDetailFromReader(IDataReader reader)
        {
            Entities.SubjectDetail subjectDetail = new Entities.SubjectDetail();
            subjectDetail.ClassID = (string)reader["cClassID"];
            subjectDetail.StaffID = (string)reader["cStaffID"];
            subjectDetail.SubjectID = (string)reader["cSubjectID"];
            return subjectDetail;
        }

        public void InsertSubjectDetail(Entities.SubjectDetail subjectDetail)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcInsertSubjectDetail", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cSubjectID", subjectDetail.SubjectID));
                command.Parameters.Add(new SqlParameter("@cStaffID", subjectDetail.StaffID));
                command.Parameters.Add(new SqlParameter("@cClassID", subjectDetail.ClassID));

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void UpdateSubjectDetail(Entities.SubjectDetail subjectDetail)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcUpdateSubjectDetail", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cSubjectID", subjectDetail.SubjectID));
                command.Parameters.Add(new SqlParameter("@cStaffID", subjectDetail.StaffID));
                command.Parameters.Add(new SqlParameter("@cClassID", subjectDetail.ClassID));

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void DeleteSubjectDetail(Entities.SubjectDetail subjectDetail)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeleteSubjectDetail", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cSubjectID", subjectDetail.SubjectID));
                command.Parameters.Add(new SqlParameter("@cStaffID", subjectDetail.StaffID));
                command.Parameters.Add(new SqlParameter("@cClassID", subjectDetail.ClassID));

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public List<Entities.SubjectDetail> GetAllSubjectDetails()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllSubjectDetails", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                List<Entities.SubjectDetail> subjectDetails = new List<Entities.SubjectDetail>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        subjectDetails.Add(CreateSubjectDetailFromReader(reader));
                    }
                }
                return subjectDetails;
            }
        }

        public List<Entities.SubjectDetail> GetSubjectDetailsByClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetSubjectDetailsByClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cClassID", sClassID));

                connection.Open();

                List<Entities.SubjectDetail> subjectDetails = new List<Entities.SubjectDetail>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        subjectDetails.Add(CreateSubjectDetailFromReader(reader));
                    }
                }
                return subjectDetails;
            }
        }

        public List<Entities.SubjectDetail> GetSubjectDetailsByStaffID(string sStaffID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetSubjectDetailsByStaffID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStaffID", sStaffID));

                connection.Open();
                List<Entities.SubjectDetail> subjectDetails = new List<Entities.SubjectDetail>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        subjectDetails.Add(CreateSubjectDetailFromReader(reader));
                    }
                }
                return subjectDetails;
            }
        }

        public Entities.SubjectDetail GetSubjectDetailByClassIDAndSubjectID(string sSubjectID, string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetSubjectDetailByClassIDAndSubjectID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cClassID", sClassID));
                command.Parameters.Add(new SqlParameter("@cSubjectID", sSubjectID));

                connection.Open();
                Entities.SubjectDetail subjectDetail = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        subjectDetail = CreateSubjectDetailFromReader(reader);
                    }
                }
                return subjectDetail;
            }
        }
    }
}
